# coding=utf-8
from sqlalchemy import func

from common import slave
from common.utils.decorator import slave_wrapper
from common.withdraw.model import WithdrawOrder, WITHDRAW_ORDER_STATUS


@slave_wrapper
def get_withdraw_daily_data(mch_id, started_at, ended_at):
    """ 提现整体数据[每日] """
    query = slave.session.query(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.status,
        func.count(WithdrawOrder.id),
        func.sum(WithdrawOrder.amount)). \
        filter(WithdrawOrder.created_at >= started_at).filter(WithdrawOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(WithdrawOrder.mch_id == mch_id)

    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.status)
    return items


@slave_wrapper
def get_withdraw_hourly_data(mch_id, started_at, ended_at):
    """ 提现整体数据[每时] """
    query = slave.session.query(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.status,
        func.count(WithdrawOrder.id),
        func.sum(WithdrawOrder.amount)). \
        filter(WithdrawOrder.created_at >= started_at).filter(WithdrawOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(WithdrawOrder.mch_id == mch_id)

    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.status)
    return items


@slave_wrapper
def get_withdraw_effi_hourly_data(mch_id, started_at, ended_at):
    """ 提现效率数据[每时] """
    query = slave.session.query(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.third_type,
        func.count(WithdrawOrder.id),
        func.sum(func.time_to_sec(func.timediff(WithdrawOrder.updated_at, WithdrawOrder.created_at)))). \
        filter(WithdrawOrder.status == WITHDRAW_ORDER_STATUS.DONE). \
        filter(WithdrawOrder.created_at >= started_at).filter(WithdrawOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(WithdrawOrder.mch_id == mch_id)

    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.third_type)
    return items


@slave_wrapper
def get_withdraw_effi_daily_data(mch_id, started_at, ended_at):
    """ 提现效率数据[每天] """
    query = slave.session.query(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.third_type,
        func.count(WithdrawOrder.id),
        func.sum(func.time_to_sec(func.timediff(WithdrawOrder.updated_at, WithdrawOrder.created_at)))). \
        filter(WithdrawOrder.status == WITHDRAW_ORDER_STATUS.DONE). \
        filter(WithdrawOrder.created_at >= started_at).filter(WithdrawOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(WithdrawOrder.mch_id == mch_id)

    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(WithdrawOrder.created_at, '+00:00', '+08:00')),
        WithdrawOrder.third_type)
    return items
